package edu.bw.dao.impl;

import edu.bw.dao.RoleDao;
import edu.bw.dto.SelectRoleByConditionForm;
import edu.bw.pojo.Role;
import edu.bw.dto.UpdateRoleForm;
import edu.bw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class RoleDaoImpl implements RoleDao {
    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    @Override
    public List<Map<String, Object>> selectRoleByCondition(SelectRoleByConditionForm selectCondition) {
        //1.拼装sql语句
        StringBuilder sql = new StringBuilder(" SELECT r.id, r.role_name AS roleName, COUNT(u.id) AS users, JSON_LENGTH(r.permissions) AS permissions, r.`desc`, r.systemic FROM bw_zzy_role r " +
                " LEFT JOIN bw_zzy_users u ON JSON_CONTAINS(u.role, CONVERT(r.id,CHAR)) WHERE 1=1 ");

        List<Object> queryParams = new ArrayList<>();

        if (selectCondition.getRoleName()!=null && !selectCondition.getRoleName().isEmpty()) {
            sql.append(" AND r.role_name LIKE ? ");
            queryParams.add("%" + selectCondition.getRoleName() + "%");
        }
        sql.append(" GROUP BY r.id ORDER BY r.id LIMIT ?,?");
        queryParams.add((selectCondition.getPage() - 1) * selectCondition.getLength());
        queryParams.add(selectCondition.getLength());

        try {
            return qr.query(sql.toString(), new MapListHandler(), queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Long selectRoleByConditionCount(SelectRoleByConditionForm selectCondition) {
        StringBuilder sql = new StringBuilder(" SELECT COUNT(*) FROM ( SELECT r.id FROM bw_zzy_role r LEFT JOIN bw_zzy_users u " +
                "ON JSON_CONTAINS(u.role, CONVERT(r.id,CHAR)) WHERE 1=1 ");

        List<Object> queryParams = new ArrayList<>();

        if (selectCondition.getRoleName()!=null && !selectCondition.getRoleName().isEmpty()) {
            sql.append(" AND r.role_name LIKE ? ");
            queryParams.add("%" + selectCondition.getRoleName() + "%");
        }

        sql.append(" GROUP BY r.id ) AS temp");

        try {
            return qr.query(sql.toString(),new ScalarHandler<Long>(),queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer insert(Role role) {
        StringBuilder sql = new StringBuilder("INSERT INTO bw_zzy_role SET role_name = ? , permissions = ? ");

        List<Object> insertParams = new ArrayList<>();
        insertParams.add(role.getRoleName());
        insertParams.add(role.getPermissions());

        // desc在页面为非必填项,当有值的时候我们再插入其值,无值不插入
        if (role.getDesc()!=null && !role.getDesc().isEmpty()) {
            sql.append(" , `desc`= ? ");
            insertParams.add(role.getDesc());
        }
        try {
            return qr.update(sql.toString(), insertParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> selectById(Integer id) {
        try {
            return qr.query("SELECT id,role_name AS roleName,permissions,`desc`,default_permissions AS defaultPermissions\n" +
                    "FROM bw_zzy_role WHERE id=?", new MapListHandler(), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer update(UpdateRoleForm updateRoleForm) {
        try {
            return qr.update("UPDATE bw_zzy_role SET role_name=? , `desc` = ? , permissions = ? WHERE id=?",
                    updateRoleForm.getRoleName(),
                    updateRoleForm.getDesc(),
                    updateRoleForm.getPermissions(),
                    updateRoleForm.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * SELECT IF(SUM(temp.users)>0,FALSE,TRUE) AS result FROM(
     * 	SELECT COUNT(u.id) AS users
     * 	FROM bw_zzy_role r
     * 	INNER JOIN bw_zzy_users u ON JSON_CONTAINS(u.role,CONVERT(r.id,CHAR))
     * 	WHERE r.id IN (11,12)
     * 	GROUP BY r.id
     * ) temp
     * @param ids 待删除的角色的id
     * @return
     */
    @Override
    public boolean selectCanDelete(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("SELECT IF(SUM(temp.users)>0,FALSE,TRUE) AS result FROM(\n" +
                "\tSELECT COUNT(u.id) AS users\n" +
                "\tFROM bw_zzy_role r \n" +
                "\tINNER JOIN bw_zzy_users u ON JSON_CONTAINS(u.role,CONVERT(r.id,CHAR))\n" +
                "\tWHERE r.id IN (");

        sql.append(ids.stream().map(id -> "?").collect(Collectors.joining(", ")))//11,12 => ? ? => ?, ?
                .append(")\n" +
                        "\tGROUP BY r.id\n" +
                        ") temp");

        try {
            Long result = qr.query(sql.toString(), new ScalarHandler<Long>(), ids.toArray());
            return result == 1 ? true : false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * -- 删除角色
     * DELETE FROM bw_zzy_role
     * WHERE id in (11,12)
     * AND systemic = FALSE
     * @param ids 待删除的角色的id
     * @return
     */
    @Override
    public Integer deleteRoleByIds(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM bw_zzy_role\n" +
                "WHERE id in (");
        sql.append(ids.stream().map(id -> "?").collect(Collectors.joining(", ")))
                .append(")\n" +
                        "AND systemic = FALSE");

        try {
            return qr.update(sql.toString(), ids.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * SELECT id, role_name AS roleName
     * FROM bw_zzy_role
     * ORDER BY id
     * @return
     */
    @Override
    public List<Map<String, Object>> selectAllRole() {
        try {
            return qr.query("SELECT id, role_name AS roleName \n" +
                    "FROM bw_zzy_role \n" +
                    "ORDER BY id", new MapListHandler());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


}
